package br.jonasLab.financistoweb.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.jonasLab.financistoweb.utils.Utils;
import br.jonasLab.financistoweb.vo.ExtratoAplicMesAnoVO;

public class ExtratoAplicMesAnoDAO extends BaseDAO{
	
	public List<ExtratoAplicMesAnoVO> extratoMesAnoFgts() throws Exception {
		List<ExtratoAplicMesAnoVO> list  = new ArrayList<ExtratoAplicMesAnoVO>();
		String 
		SQL = "SELECT " +
				"strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO," +
				"SUM(CASE WHEN trf.category_id=118 THEN trf.from_amount ELSE 0 END) AS VR_DEPOSITO," +
				"SUM(CASE WHEN trf.category_id=46  THEN trf.from_amount ELSE 0 END) AS VR_JUROS," +
				"SUM(CASE WHEN trf.category_id=32  THEN trf.from_amount ELSE 0 END) AS VR_SAQUE," +
				"SUM(from_amount) AS VR_SALDO_MOVIMENTO " +
			"FROM transactions trf " +
			"WHERE " +
				"trf.from_account_id = 13 " +
				"AND trf.category_id in (118,46,32) " +
			"GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') " +
			"ORDER BY trf.datetime;";
		
		PreparedStatement ps = this.getConnection().prepareStatement(SQL);
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			list.add(retVo(rs));
		}
		rs.close();
        ps.close();
		return list;
	}
	
	public List<ExtratoAplicMesAnoVO> extratoAplicacoes() throws SQLException{
		List<ExtratoAplicMesAnoVO> list  = new ArrayList<ExtratoAplicMesAnoVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO,");
		sql.append("    SUM(CASE WHEN trf.category_id=3 THEN ");
		sql.append("		(CASE WHEN trf.from_amount<0 THEN trf.from_amount*-1 ELSE trf.from_amount END) ");
		sql.append("        ELSE 0 END) AS VR_DEPOSITO,");
		sql.append("    SUM(CASE WHEN trf.category_id=45  THEN trf.from_amount ELSE 0 END) AS VR_JUROS,");
		sql.append("    SUM(CASE WHEN trf.category_id=126 THEN trf.from_amount ELSE 0 END) AS VR_SAQUE,");
		sql.append("    SUM(from_amount)/100.00 AS VR_SALDO_MOVIMENTO ");
		sql.append("FROM transactions trf ");
		sql.append("WHERE ");
		sql.append("    trf.from_account_id = 7 OR ");
		sql.append("    trf.to_account_id = 7 ");
		sql.append("GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime')    ");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			list.add(retVo(rs));
		}
		rs.close();
        ps.close();
		return list;
	}
	
	public List<ExtratoAplicMesAnoVO> extratoIlka() throws SQLException{
		
		List<ExtratoAplicMesAnoVO> list  = new ArrayList<ExtratoAplicMesAnoVO>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO,");
		sql.append("	SUM(CASE WHEN trf.category_id=38 AND trf.from_account_id=6 THEN trf.to_amount ELSE 0 END) AS VR_DEPOSITO,");
		sql.append("	SUM(CASE WHEN trf.category_id=5 THEN trf.from_amount*-1 ELSE 0 END) AS VR_JUROS, ");
		sql.append("	SUM(CASE WHEN trf.category_id=38 AND trf.to_account_id=6 THEN trf.from_amount ELSE 0 END) AS VR_SAQUE, ");
		sql.append("	0.0 VR_SALDO_MOVIMENTO ");
		sql.append("FROM transactions trf ");
		sql.append("WHERE ");
		sql.append("	trf.from_account_id = 6 ");
		sql.append("    OR trf.to_account_id = 6 ");
		sql.append("GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime')");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			list.add(retVo(rs));
		}
		rs.close();
        ps.close();
		return list;
	}
	
	private ExtratoAplicMesAnoVO retVo(ResultSet rs) throws SQLException {
		ExtratoAplicMesAnoVO vo = new ExtratoAplicMesAnoVO();
		vo.setMesAno(rs.getString("MES_ANO"));
		vo.setDeposito(Utils.divideValores(rs.getDouble("VR_DEPOSITO"), 100, 2));
		vo.setJuros(Utils.divideValores(rs.getDouble("VR_JUROS"), 100, 2));
		vo.setSaque(Utils.divideValores(rs.getDouble("VR_SAQUE"), 100, 2));
		vo.setSaldoMovimento(Utils.divideValores(rs.getDouble("VR_SALDO_MOVIMENTO"), 100, 2));
		return vo;
	}
}
